# 在文件顶部添加
import json
import dash
from dash import State
import dash_bootstrap_components as dbc
from dash import Input, Output, callback,dcc,html
import pandas as pd
import plotly.graph_objects as go
from flask import session
import os
from models import Organization
import plotly.express as px
from dash.dependencies import Input, Output
from flask import session
from collections import Counter
from server import db
from models import Organization, RepairRecord 


def register_callbacks(dash_app):
    # 回调函数：根据下拉框选择更新圆环图和统计数据
    @dash_app.callback(
        [Output('branch-asset-distribution-chart', 'figure'),
         Output('branch-asset-category-chart', 'figure'),  # 添加第二个图表的输出
         Output('branch-total-amount-display', 'children'),
         Output('branch-total-count-display', 'children')],
        [Input('branch-amount-filter', 'value'),
         Input('branch-department-filter', 'value')]
    )
    def update_asset_distribution(selected_price_range, selected_department):

        # 创建维修商面板（上排第3列）
# 假设你有start_date, end_date变量（可从DatePickerRange获取）
        def get_provider_options(start_date, end_date, org_shortname_c=None):
            from models import db, RepairRecord
            query = db.session.query(RepairRecord.repair_company)
            if org_shortname_c:
                query = query.filter(RepairRecord.org_shortname_c==org_shortname_c)
            if start_date:
                query = query.filter(RepairRecord.report_date >= start_date)
            if end_date:
                query = query.filter(RepairRecord.report_date <= end_date)
            query = query.filter(RepairRecord.is_active==True)
            companies = sorted(set([r.repair_company for r in query.all() if r.repair_company]))
            options = [{'label': c, 'value': c} for c in companies]
            # options.insert(0, {'label': '全部维修', 'value': 'all'})
            return options
        # 获取当前机构信息
        org_code = session.get('current_org_code', session.get('user_org_code', ''))
        print(f"p21 org_code: {org_code}")
        
        # 初始化空图表
        empty_fig = go.Figure(data=[go.Pie(
            labels=['暂无数据'],
            values=[1],
            hole=0.7,
            marker_colors=['#D3D3D3'],
            textinfo='none'
        )])
        empty_fig.update_layout(
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            margin=dict(t=10, b=10, l=10, r=10),
            height=180,  # 调整高度与charts1.py一致
            showlegend=False
        )
        
        if not org_code:
            # 如果没有机构信息，返回空数据
            return empty_fig, empty_fig, "0.00万元", "0台"
        
        # 获取机构信息
        org = Organization.query.filter_by(org_code=org_code).first()
        if not org:
            # 返回空数据
            return empty_fig, empty_fig, "0.00万元", "0台"
        
        # 构建Excel文件路径
        output_dir = os.path.join(os.getcwd(), "modules", "statistic", "xlsx3")
        excel_path = os.path.join(output_dir, f"{org.short_name_c}_科室和金额区间统计.xlsx")
        
        # 初始化数据
        asset_labels = []
        asset_values = []
        category_labels = []  # 设备分类标签
        category_values = []  # 设备分类数量
        total_amount = "0.00万元"
        total_count = "0台"
        
        if os.path.exists(excel_path):
            try:
                df = pd.read_excel(excel_path, sheet_name="科室和金额区间统计")
                # 只保留最新日期的数据
                if '日期' in df.columns:
                    latest_date = df['日期'].max()
                    df = df[df['日期'] == latest_date]
                
                # 过滤掉合计行
                df_filtered = df[df['科室'] != '合计'].copy()
                
                # 根据科室筛选
                if selected_department and selected_department != 'all':
                    df_filtered = df_filtered[df_filtered['科室'] == selected_department]
                
                # 根据价格区间筛选数据
                if selected_price_range and selected_price_range != 'all':
                    # 根据选择的价格区间，只显示该区间的数据
                    count_column = f"{selected_price_range}_台数"
                    amount_column = f"{selected_price_range}_金额"
                    
                    if count_column in df_filtered.columns:
                        # 只显示该价格区间有设备的科室
                        df_filtered = df_filtered[df_filtered[count_column] > 0]
                        
                        for _, row in df_filtered.iterrows():
                            dept = row['科室']
                            count = row[count_column]
                            if pd.notna(dept) and pd.notna(count) and count > 0:
                                asset_labels.append(f"{dept}({selected_price_range})")
                                asset_values.append(count)
                        
                        # 计算该价格区间的总计
                        total_count_val = df_filtered[count_column].sum()
                        total_amount_val = df_filtered[amount_column].sum() if amount_column in df_filtered.columns else 0
                        
                        total_count = f"{int(total_count_val)}台"
                        total_amount = f"{total_amount_val:.2f}万元"
                else:
                    # 显示所有科室的设备总数
                    for _, row in df_filtered.iterrows():
                        dept = row['科室']
                        count = row['设备合计台数'] if '设备合计台数' in df_filtered.columns else 0
                        if pd.notna(dept) and pd.notna(count) and count > 0:
                            asset_labels.append(dept)
                            asset_values.append(count)
                    
                    # 计算总计
                    total_count_val = df_filtered['设备合计台数'].sum() if '设备合计台数' in df_filtered.columns else 0
                    total_amount_val = df_filtered['设备合计金额'].sum() if '设备合计金额' in df_filtered.columns else 0
                    
                    total_count = f"{int(total_count_val)}台"
                    total_amount = f"{total_amount_val:.2f}万元"
                
                # 处理设备分类数据
                # 读取设备分类数据的Excel文件
                category_excel_path = os.path.join(os.getcwd(), "modules", "statistic", "xlsx", f"{org.short_name_c}_设备分类及价格区间统计.xlsx")
                
                if os.path.exists(category_excel_path):
                    try:
                        df_category = pd.read_excel(category_excel_path)
                        # 只保留最新日期的数据
                        if '日期' in df_category.columns:
                            latest_date = df_category['日期'].max()
                            df_category = df_category[df_category['日期'] == latest_date]
                        
                        # 过滤掉合计行
                        df_category = df_category[df_category['设备分类'] != '合计数据']
                        
                        # 根据价格区间筛选数据
                        if selected_price_range and selected_price_range != 'all' and '设备分类' in df_category.columns:
                            # 根据选择的价格区间，只显示该区间的数据
                            count_column = f"{selected_price_range}_台数"
                            
                            if count_column in df_category.columns:
                                # 只处理该价格区间有设备的分类
                                df_category_filtered = df_category[df_category[count_column] > 0]
                                
                                # 按设备分类分组并汇总该价格区间的台数
                                category_counts = df_category_filtered.groupby('设备分类')[count_column].sum().reset_index()
                                
                                for _, row in category_counts.iterrows():
                                    category = row['设备分类']
                                    count = row[count_column]
                                    if pd.notna(category) and pd.notna(count) and count > 0:
                                        category_labels.append(f"{category}({selected_price_range})")
                                        category_values.append(count)
                        else:
                            # 如果没有选择特定价格区间，则显示所有价格区间的总和
                            if '设备分类' in df_category.columns:
                                # 识别所有价格区间的台数列
                                count_columns = [col for col in df_category.columns if col.endswith('_台数')]
                                
                                # 为每个设备分类计算所有价格区间的台数总和
                                df_category['总台数'] = df_category[count_columns].sum(axis=1)
                                
                                # 按设备分类分组并汇总总台数
                                category_counts = df_category.groupby('设备分类')['总台数'].sum().reset_index()
                                
                                for _, row in category_counts.iterrows():
                                    category = row['设备分类']
                                    count = row['总台数']
                                    if pd.notna(category) and pd.notna(count) and count > 0:
                                        category_labels.append(category)
                                        category_values.append(count)
                    except Exception as e:
                        print(f"p139读取设备分类Excel文件出错: {e}")
                        asset_labels = ['读取数据出错']
                        asset_values = [1]
                        category_labels = ['读取数据出错']
                        category_values = [1]
            except Exception as e:
                print(f"p145读取Excel文件出错: {e}")
                asset_labels = ['读取数据出错']
                asset_values = [1]
        # 如果没有数据，显示默认信息
        if not asset_labels:
            asset_labels = ['暂无数据']
            asset_values = [1]
        
        if not category_labels:
            category_labels = ['暂无分类数据']
            category_values = [1]
        
        # 生成颜色
        asset_colors = px.colors.qualitative.Set3[:len(asset_labels)]
        category_colors = px.colors.qualitative.Pastel[:len(category_labels)]
        
        # 创建科室分布圆环图
        asset_pie_chart = go.Figure(data=[go.Pie(
            labels=asset_labels,
            values=asset_values,
            hole=0.7,
            marker_colors=asset_colors,
            textinfo='none'
        )])
        
        asset_pie_chart.update_layout(
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            margin=dict(t=10, b=10, l=10, r=10),
            height=180,  # 调整高度与charts1.py一致
            showlegend=False
        )
        
        # 创建设备分类圆环图
        category_pie_chart = go.Figure(data=[go.Pie(
            labels=category_labels,
            values=category_values,
            hole=0.7,
            marker_colors=category_colors,
            textinfo='none'
        )])
        
        category_pie_chart.update_layout(
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            margin=dict(t=10, b=10, l=10, r=10),
            height=180,  # 调整高度与charts1.py一致
            showlegend=False
        )
        
        return asset_pie_chart, category_pie_chart, total_amount, total_count
    

    # 回调函数：根据下拉框选择更新维修商面板，上排第3列
    @dash_app.callback(
        Output('branch-repair-provider-panel', 'children'),
        [Input('branch-date-picker-range', 'start_date'),
         Input('branch-date-picker-range', 'end_date'),
         Input('provider-repaircompany-filter', 'value'),
         Input('repair-type-filter', 'value')],
    )
    def update_repair_provider_panel(start_date, end_date, selected_provider, repair_type):
        from .charts1 import create_repair_provider_panel_layout
        user_org_code = session.get('current_org_code', session.get('user_org_code', None))
        org_shortname_c = None
        org = None
        if user_org_code and user_org_code.upper() != 'HPQWJW':
            org = db.session.query(Organization.short_name_c).filter(Organization.org_code == user_org_code).first()
            if org:
                org_shortname_c = org.short_name_c
        # 获取所有维修公司用于下拉选项
        all_query = db.session.query(RepairRecord.repair_company)
        if org_shortname_c:
            all_query = all_query.filter(RepairRecord.org_shortname_c == org_shortname_c)
        if start_date:
            all_query = all_query.filter(RepairRecord.report_date >= start_date)
        if end_date:
            all_query = all_query.filter(RepairRecord.report_date <= end_date)
        all_repair_companies = [r.repair_company for r in all_query.filter(RepairRecord.is_active == True).all() if r.repair_company]
        all_company_counter = Counter(all_repair_companies)
        all_labels = list(all_company_counter.keys())
        # 根据下拉选项过滤数据
        query = db.session.query(RepairRecord)
        if org_shortname_c:
            query = query.filter(RepairRecord.org_shortname_c == org_shortname_c)
        if start_date:
            query = query.filter(RepairRecord.report_date >= start_date)
        if end_date:
            query = query.filter(RepairRecord.report_date <= end_date)
        query = query.filter(RepairRecord.is_active == True)
        if selected_provider and selected_provider != 'all_repair_companies':
            query = query.filter(RepairRecord.repair_company == selected_provider)
        filtered_records = query.all()
        # 维修次数
        total_repair_count = len(filtered_records)
        # 重复维修次数（所有重复出现的产品ID的记录总数减去重复产品ID的个数）
        product_ids = [r.product_id for r in filtered_records if r.product_id]
        product_counter = Counter(product_ids)
        # 找出重复维修的产品ID（出现次数>=2的产品ID）
        repeat_product_ids = [pid for pid, count in product_counter.items() if count >= 2]
        # 计算重复维修次数：所有重复产品的维修记录总数 - 重复产品的个数
        repeat_repair_count = sum(product_counter[pid] for pid in repeat_product_ids) - len(repeat_product_ids)
        # 维修商分布数据
        repair_companies = [r.repair_company for r in filtered_records if r.repair_company]
        company_counter = Counter(repair_companies)
        vendor_labels = list(company_counter.keys())
        vendor_values = list(company_counter.values())
        if not vendor_labels:
            vendor_labels = ['无数据']
            vendor_values = [1]
        # 设备类型分布数据
        product_categorys = [r.product_category for r in filtered_records if r.product_category]
        type_counter = Counter(product_categorys)
        type_labels = list(type_counter.keys())
        type_values = list(type_counter.values())
        if not type_labels:
            type_labels = ['无数据']
            type_values = [1]
        # 创建维修商分布图
        vendor_fig = go.Figure(data=[go.Pie(
            labels=vendor_labels,
            values=vendor_values,
            hole=0.7,
            marker_colors=['#00d4ff', '#a78bfa', '#ffaa00', '#ff6384', '#36a2eb', '#ffce56', '#4bc0c0', '#9966ff', '#c9cbcf'],
            textinfo='none'
        )])
        vendor_fig.update_layout(
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            margin=dict(t=10, b=10, l=10, r=10),
            height=200,
            showlegend=False
        )
        # 创建设备类型分布图
        type_fig = go.Figure(data=[go.Pie(
            labels=type_labels,
            values=type_values,
            hole=0.7,
            marker_colors=['#00d4ff', '#a78bfa', '#ffaa00', '#ff6384', '#36a2eb', '#ffce56', '#4bc0c0', '#9966ff', '#c9cbcf'],
            textinfo='none'
        )])
        type_fig.update_layout(
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            margin=dict(t=10, b=10, l=10, r=10),
            height=200,
            showlegend=False
        )
        # 使用布局函数创建面板
        return [create_repair_provider_panel_layout(selected_provider, total_repair_count, repair_type, repeat_repair_count=repeat_repair_count)]
                
            
    @dash_app.callback(
        [Output('provider-vendor-chart', 'figure'),
         Output('provider-equipment-chart', 'figure')],
        [Input('provider-repaircompany-filter', 'value'),
         Input('branch-date-picker-range', 'start_date'),
         Input('branch-date-picker-range', 'end_date'),
         Input('repair-type-filter', 'value')]
    )
    def update_provider_charts(selected_provider, start_date, end_date, repair_type):
        user_org_code = session.get('current_org_code', session.get('user_org_code', None))
        org_shortname_c = None
        org = None
        if user_org_code and user_org_code.upper() != 'HPQWJW':
            org = db.session.query(Organization.short_name_c).filter(Organization.org_code == user_org_code).first()
            if org:
                org_shortname_c = org.short_name_c
        
        # 根据下拉选项过滤数据
        query = db.session.query(RepairRecord)
        if org_shortname_c:
            query = query.filter(RepairRecord.org_shortname_c == org_shortname_c)
        if start_date:
            query = query.filter(RepairRecord.report_date >= start_date)
        if end_date:
            query = query.filter(RepairRecord.report_date <= end_date)
        query = query.filter(RepairRecord.is_active == True)
        if selected_provider and selected_provider != 'all_repair_companies':
            query = query.filter(RepairRecord.repair_company == selected_provider)
        
        filtered_records = query.all()
        
        # 如果选择了重复维修，则只显示重复维修的数据
        if repair_type == 'repeat_repair':
            # 获取所有产品ID及其出现次数
            product_ids = [r.product_id for r in filtered_records if r.product_id]
            product_counter = Counter(product_ids)
            
            # 找出重复维修的产品ID（出现次数>=2的产品ID）
            repeat_product_ids = [pid for pid, count in product_counter.items() if count >= 2]
            
            # 创建一个新的记录列表，只包含重复维修产品的第二次及以后的维修记录
            repeat_records = []
            processed_ids = set()  # 用于跟踪已处理的产品ID
            
            for record in filtered_records:
                if not record.product_id or record.product_id not in repeat_product_ids:
                    continue  # 跳过非重复维修的记录
                
                # 对于重复维修的产品，跳过第一次维修记录
                if record.product_id not in processed_ids:
                    processed_ids.add(record.product_id)
                else:
                    repeat_records.append(record)
            
            filtered_records = repeat_records
        
        # 维修商分布数据
        repair_companies = [r.repair_company for r in filtered_records if r.repair_company]
        company_counter = Counter(repair_companies)
        vendor_labels = list(company_counter.keys())
        vendor_values = list(company_counter.values())
        
        if not vendor_labels:
            vendor_labels = ['无数据']
            vendor_values = [1]
        
        # 设备类型分布数据
        product_categorys = [r.product_category for r in filtered_records if r.product_category]
        type_counter = Counter(product_categorys)
        type_labels = list(type_counter.keys())
        type_values = list(type_counter.values())
        
        if not type_labels:
            type_labels = ['无数据']
            type_values = [1]
        
        # 创建维修商分布图
        vendor_fig = go.Figure(data=[go.Pie(
            labels=vendor_labels,
            values=vendor_values,
            hole=0.7,
            marker_colors=['#00d4ff', '#a78bfa', '#ffaa00', '#ff6384', '#36a2eb', '#ffce56', '#4bc0c0', '#9966ff', '#c9cbcf'],
            textinfo='none'
        )])
        vendor_fig.update_layout(
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            margin=dict(t=10, b=10, l=10, r=10),
            height=200,
            showlegend=False
        )
        
        # 创建设备类型分布图
        type_fig = go.Figure(data=[go.Pie(
            labels=type_labels,
            values=type_values,
            hole=0.7,
            marker_colors=['#00d4ff', '#a78bfa', '#ffaa00', '#ff6384', '#36a2eb', '#ffce56', '#4bc0c0', '#9966ff', '#c9cbcf'],
            textinfo='none'
        )])
        type_fig.update_layout(
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            margin=dict(t=10, b=10, l=10, r=10),
            height=200,
            showlegend=False
        )
        
        return vendor_fig, type_fig

    
    # 回调函数：更新设备运维面板的巡检和保养数据
    @dash_app.callback(
        [
            Output('inspection-count-display', 'children'),
            Output('maintenance-count-display', 'children'),
            Output('inspection-completion-chart', 'figure'),
            Output('maintenance-completion-chart', 'figure'),
            Output('inspection-completion-rate', 'children'),
            Output('maintenance-completion-rate', 'children')
        ],
        [Input('branch-inspection-filter', 'value')]
    )
    def update_equipment_operation_panel(selected_phase):
        # 获取当前机构信息
        org_code = session.get('current_org_code', session.get('user_org_code', ''))
        org = Organization.query.filter_by(org_code=org_code).first()
        org_short_name = org.short_name_c if org else None
        
        # 初始化返回值
        inspection_count = 0
        maintenance_count = 0
        inspection_rate = 0
        maintenance_rate = 0
        equipment_count = 0
        
        # 创建默认图表
        def create_completion_chart(completion_rate=0):
            completed = completion_rate
            not_completed = 100 - completion_rate
            
            fig = go.Figure(data=[go.Pie(
                labels=['完成', '未完成'],
                values=[completed, not_completed],
                hole=0.7,
                marker_colors=['#00ff88', '#ff6b6b'],
                textinfo='none'
            )])
            
            fig.update_layout(
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)',
                margin=dict(t=10, b=10, l=10, r=10),
                height=200,
                showlegend=False
            )
            
            return fig
        
        # 默认图表
        inspection_fig = create_completion_chart(0)
        maintenance_fig = create_completion_chart(0)
        
        try:
            # 读取Excel文件
            excel_path = os.path.join(os.getcwd(), "modules", "statistic", "xlsx2", "各机构保养巡检次数统计.xlsx")
            
            if os.path.exists(excel_path) and org_short_name:
                df = pd.read_excel(excel_path, sheet_name="Sheet1")
                
                # 查找当前机构的数据
                org_data = df[df['机构简称'] == org_short_name]
                
                if not org_data.empty:
                    # 获取有效设备数量
                    equipment_count = org_data['有效设备数量'].values[0]
                    
                    # 根据选择的阶段获取巡检和保养次数
                    if selected_phase == 'phase1':  # 一期
                        inspection_count = org_data['一期巡检'].values[0]
                        maintenance_count = org_data['一期保养'].values[0]
                    else:  # 二期
                        inspection_count = org_data['二期巡检'].values[0]
                        maintenance_count = org_data['二期保养'].values[0]
                    
                    # 计算完成率
                    if equipment_count > 0:
                        inspection_rate = (inspection_count / equipment_count) * 100
                        maintenance_rate = (maintenance_count / equipment_count) * 100
                    
                    # 更新图表
                    inspection_fig = create_completion_chart(inspection_rate)
                    maintenance_fig = create_completion_chart(maintenance_rate)
        except Exception as e:
            print(f"读取巡检保养数据出错: {e}")
        
        # 格式化返回值
        inspection_count_display = f"{int(inspection_count)}"
        maintenance_count_display = f"{int(maintenance_count)}"
        inspection_rate_display = f"{inspection_rate:.1f}%"
        maintenance_rate_display = f"{maintenance_rate:.1f}%"
        
        return (
            inspection_count_display,
            maintenance_count_display,
            inspection_fig,
            maintenance_fig,
            inspection_rate_display,
            maintenance_rate_display
        )